package com.google.code.rgbgoesbetween.data.sqlite;

import java.util.ArrayList;

import com.google.code.rgbgoesbetween.data.AccountStatus;
import com.google.code.rgbgoesbetween.data.Datastore;
import com.google.code.rgbgoesbetween.data.Item;
import com.google.code.rgbgoesbetween.data.Profile;
import com.google.code.rgbgoesbetween.data.QueryStatus;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

// *SNIP*
//SQLite is available on every Android device. Using an SQLite database in 
//Android does not require any database setup or administration. You only 
//have to define the SQL statements for creating and updating the database. 
//Afterwards the database is automatically managed for you by the Android 
//platform. 
//
//Access to an SQLite database involves accessing the filesystem. 
//This can be slow. Therefore it is recommended to perform database operations 
//asynchronously, for example inside the AsyncTask class. If your application 
//creates a database, this database is by default saved in the directory 
// DATA/data/APP_NAME/databases/FILENAME.

public class SQLiteDatastore implements Datastore {

	// Database fields
	  private SQLiteDatabase database;
	  private SQLiteDBManager dbManager;
	  
	  private String[] allProfileColumns = new String[] { 
			  SQLiteDBManager.COLUMN_PROFILE_ID, SQLiteDBManager.COLUMN_PROFILE_USER,
			  SQLiteDBManager.COLUMN_PROFILE_PASS, SQLiteDBManager.COLUMN_PROFILE_STATUS,
			  SQLiteDBManager.COLUMN_PROFILE_ADDRESS, SQLiteDBManager.COLUMN_PROFILE_PHONE, 
			  SQLiteDBManager.COLUMN_PROFILE_EMAIL
	  };
	  
	  private String[] allReportColumns = new String[] {
			  SQLiteDBManager.COLUMN_REPORT_ID,
			  SQLiteDBManager.COLUMN_REPORT_REPORTERID,
			  SQLiteDBManager.COLUMN_REPORT_ITEMID
	  };

	  private AccountStatus[] profileEnums = AccountStatus.values();
	  
	  public SQLiteDatastore(Context context) {
	    dbManager = new SQLiteDBManager(context);
	  }

	  public void openWritableDB() throws SQLException {
	    database = dbManager.getWritableDatabase();
	  }
	  
	  public void openReadableDB() throws SQLException {
		  database = dbManager.getReadableDatabase();
	  }

	  public void closeDB() {
	    dbManager.close();
	  }
	
	@Override
	public Profile getAccountInfo(String username, String passHash){
		openReadableDB();
		
		// Make a DB query.
		Cursor c = database.query(SQLiteDBManager.TABLE_PROFILES, allProfileColumns, 
				SQLiteDBManager.COLUMN_PROFILE_USER + " = '" + username + "' AND " +
			    SQLiteDBManager.COLUMN_PROFILE_PASS + " = '" + passHash + "'", 
			    null, null, null, null);
		if (c.getCount() != 1) { // Either 0 or multiple results returned
			c.close();
			closeDB();
			return null;
		}
		
		// Get the profile
		c.moveToFirst();
		Profile p = new Profile(
				c.getInt(0), c.getString(1), c.getString(2), profileEnums[c.getInt(3)],
				c.getString(4), c.getString(5), c.getString(6)
		);
		c.close();
		
		// Get the IDs of each item belonging to this profile
		c = database.query(SQLiteDBManager.TABLE_REPORTS, allReportColumns, 
				SQLiteDBManager.COLUMN_REPORT_REPORTERID + " = " + p.getId(), 
				null, null, null, null);
		int[] ids = new int[c.getCount()];
		int i = 0;
		if (c.getCount() > 0) {
			c.moveToFirst();
			ids[i++] = c.getInt(2);
			c.moveToNext();
		}
		p.setItemIDList(ids);
		
		c.close();
		closeDB();
		
		return p;
	}

	@Override
	public Profile createProfile(Profile profile) {
		// First check to see if this profile already exists by making a username query.
		openReadableDB();
		Cursor c = database.query(SQLiteDBManager.TABLE_PROFILES, allProfileColumns, 
				SQLiteDBManager.COLUMN_PROFILE_USER + " = '" + profile.getUsername() + "';",
				null, null, null, null);
		if (c.getCount() > 0)
			return null;
		c.close();
		closeDB();
		
		// Now insert the profile into the database
		openWritableDB();
		ContentValues vals = new ContentValues();
		vals.put(SQLiteDBManager.COLUMN_PROFILE_USER, profile.getUsername());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PASS, profile.getPassHash());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_STATUS, profile.getAccountStatus() != null ?
				Integer.valueOf(profile.getAccountStatus().ordinal()) : 
					Integer.valueOf(AccountStatus.STANDARD_MEMBER.ordinal()));
		vals.put(SQLiteDBManager.COLUMN_PROFILE_ADDRESS, profile.getAddress());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PHONE, profile.getPhoneNumber());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_EMAIL, profile.getEmail());
		database.insert(SQLiteDBManager.TABLE_PROFILES, null, vals);
		closeDB();
		
		return getAccountInfo(profile.getUsername(), profile.getPassHash());
	}

	@Override
	public QueryStatus updateProfile(Profile profile) {
		openWritableDB();
		ContentValues vals = new ContentValues();
		vals.put(SQLiteDBManager.COLUMN_PROFILE_USER, profile.getUsername());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PASS, profile.getPassHash());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_STATUS, profile.getAccountStatus().toString());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_ADDRESS, profile.getAddress());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PHONE, profile.getPhoneNumber());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_EMAIL, profile.getEmail());
		int rowsAffected = database.update(SQLiteDBManager.TABLE_PROFILES, vals, 
				SQLiteDBManager.COLUMN_PROFILE_ID + "=" + profile.getId(), null);
		closeDB();
		return rowsAffected == 1 ? QueryStatus.QUERY_SUCCESS: QueryStatus.QUERY_FAIL;
	}

	@Override
	public QueryStatus createItem(Item item) {
		// TODO Auto-generated method stub
		return QueryStatus.METHOD_NOT_IMPLEMENTED;
	}

	@Override
	public QueryStatus updateItem(Item item) {
		// TODO Auto-generated method stub
		return QueryStatus.METHOD_NOT_IMPLEMENTED;
	}

	@Override
	public QueryStatus deleteItem(Item item) {
		// TODO Auto-generated method stub
		return QueryStatus.METHOD_NOT_IMPLEMENTED;
	}

	@Override
	public Item requestItem(int id) {
		// TODO Auto-generated method stub
		return null;
	}

}
